﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Abp;
using Abp.Application.Services.Dto;
using Abp.Authorization;
using Abp.AutoMapper;
using Abp.Configuration;
using Abp.Domain.Repositories;
using Abp.Extensions;
using Abp.Linq.Extensions;
using LeadThing.AbpZeroTemplate.Areas.Authorization;
using LeadThing.AbpZeroTemplate.Areas.Dtos;
using LeadThing.AbpZeroTemplate.Dto;
using LeadThing.AbpZeroTemplate.Files;

namespace LeadThing.AbpZeroTemplate.Areas
{
    /// <summary>
    /// 地区服务实现
    /// </summary>
    public class AreaAppService : AbpZeroTemplateAppServiceBase, IAreaAppService
    {
        private readonly IRepository<Area, long> _areaRepository;
        private readonly IAreaListExcelExporter _areaListExcelExporter;
        private readonly IRepository<File, long> _fileRepository;
        private readonly ISqlExecuter _sqlExecuter;//sql语句接口


        private readonly AreaManage _areaManage;
        /// <summary>
        /// 构造方法
        /// </summary>
        public AreaAppService(IRepository<Area, long> areaRepository,
        AreaManage areaManage
      , IAreaListExcelExporter areaListExcelExporter
      , IRepository<File, long> fileRepository
      , ISqlExecuter sqlExecuter
  )
        {
            _areaRepository = areaRepository;
            _areaManage = areaManage;
            _areaListExcelExporter = areaListExcelExporter;
            _fileRepository = fileRepository;
            _sqlExecuter = sqlExecuter;
        }


        #region 实体的自定义扩展方法
        private IQueryable<Area> _areaRepositoryAsNoTrack => _areaRepository.GetAll().AsNoTracking();

        /// <summary>
        /// 根据层级统计特殊人群人数
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<AreaFileListDto> GetSumSpecialPersonLoadPage(GetAreaInput input)
        {
            StringBuilder sb = new StringBuilder();
            if (input.Level == 1)
            {
                sb.Append(@"select Id,Title,
                            'SumPerson' = (select count(*) from Basic.SpecialPerson where TownID=area.Id and TownID > 0 and isDeleted=0) 
                            from Basic.Area area group by Id,level,Title having Level = 1");
            }
            else if (input.Level == 2)
            {
                sb.AppendFormat(@"select Id,Title,
                                'SumPerson' = (select count(*) from Basic.SpecialPerson where TownID = {0} and AreaID=area.Id and AreaID > 0 and isDeleted=0)
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {1}", input.ParentId, input.ParentId);
            }
            else if (input.Level == 3)
            {
                sb.AppendFormat(@"select Id,Title,
                                'SumPerson' = (select count(*) from Basic.SpecialPerson where AreaID = {0} and VillageID=area.Id and VillageID > 0 and isDeleted=0)
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {1}", input.ParentId, input.ParentId);
            }
            return _sqlExecuter.SqlQuery<AreaFileListDto>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 根据层级统计矛盾纠纷人数
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<AreaFileListDto> GetSumPersonByLevelLoadPage(GetAreaInput input)
        {
            StringBuilder sb = new StringBuilder();
            if (input.Level == 1)
            {
                sb.Append(@"select Id,Title,
                            'SumPerson' = (select count(*) from Basic.DisputeInformation where TownID=area.Id and TownID > 0 and isDeleted=0) 
                            from Basic.Area area group by Id,level,Title having Level = 1");
            }
            else if (input.Level == 2)
            {
                sb.AppendFormat(@"select Id,Title,
                                'SumPerson' = (select count(*) from Basic.DisputeInformation where TownID = {0} and AreaID=area.Id and AreaID > 0 and isDeleted=0)
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {1}", input.ParentId, input.ParentId);
            }
            else if (input.Level == 3)
            {
                sb.AppendFormat(@"select Id,Title,
                                'SumPerson' = (select count(*) from Basic.DisputeInformation where AreaID = {0} and VillageID=area.Id and VillageID > 0 and isDeleted=0)
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {1}", input.ParentId, input.ParentId);
            }
            return _sqlExecuter.SqlQuery<AreaFileListDto>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 根据层级统计网格人口
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<AreaFileListDto> GetPersonPercentageByLevelLoadPage(GetAreaInput input)
        {
            StringBuilder sb = new StringBuilder();
            if (input.Level == 1)
            {
                sb.Append(@"select Id,Title,
                        'Percentage' = CAST(CONVERT(DECIMAL(18,2),(select count(*) from dbo.AbpUsers where TownID=area.Id and TownID > 0 and isDeleted=0)
                        /CAST(ISNULL(NULLIF((select count(*) from dbo.AbpUsers where TownID > 0 and isDeleted=0),0),1) AS FLOAT)*100 ) AS VARCHAR(10))
                        from Basic.Area area group by Id,level,Title having Level = 1 ");
            }
            else if (input.Level == 2)
            {
                sb.AppendFormat(@"select Id,Title,
                                'Percentage' = CAST(CONVERT(DECIMAL(18,2),(select count(*) from dbo.AbpUsers where TownID = {0} and AreaID=area.Id and AreaID > 0 and isDeleted=0)/
                                CAST(ISNULL(NULLIF((select count(*) from dbo.AbpUsers where TownID = {1} and AreaID > 0 and isDeleted=0),0),1) AS FLOAT)*100 ) AS VARCHAR(10))
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {2}", input.ParentId, input.ParentId, input.ParentId);
            }
            else if (input.Level == 3)
            {
                sb.AppendFormat(@"select Id,Title,
                                'Percentage' = CAST(CONVERT(DECIMAL(18,2),(select count(*) from dbo.AbpUsers where AreaID = {0} and VillageID=area.Id and VillageID > 0 and isDeleted=0)/
                                CAST(ISNULL(NULLIF((select count(*) from dbo.AbpUsers where AreaID = {1} and VillageID > 0 and isDeleted=0),0),1) AS FLOAT)*100 ) AS VARCHAR(10))
                                from Basic.Area area group by Id,parentId,Title
                                having parentId = {2}", input.ParentId, input.ParentId, input.ParentId);
            }

            return _sqlExecuter.SqlQuery<AreaFileListDto>(sb.ToString()).ToList();
        }

        /// <summary>
        /// 根据条件获取地区数据加载页面数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedResultDto<AreaFileListDto> GetAreaByWhereAsyncLoadPage(GetAreaInput input)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($" select TOP {input.MaxResultCount} a.*,'Address' = (select top 1 Title from Basic.Area where Id = a.ParentId) from Basic.Area a where isDeleted=0 ");

            string skip = " and Id not in(select top " + input.SkipCount + " Id from Basic.Area where isDeleted=0 {0})";
            string skip1 = "";
            var query = _areaRepository.GetAll();
            if (input.Level > 0)
            {
                sb.Append(" and Level = ");
                sb.Append(input.Level);
                skip1 += " and Level = " + input.Level;
                query = query.Where(a => a.Level == input.Level);
            }

            if (!string.IsNullOrWhiteSpace(input.CameraID))
            {
                sb.AppendFormat(" and CameraID = '{0}' ", input.CameraID);
                skip1 += string.Format(" and CameraID = '{0}' ", input.CameraID);
                query = query.Where(a => a.CameraID == input.CameraID);
            }

            if (!string.IsNullOrWhiteSpace(input.FilterText))
            {
                sb.Append(" and Title like '%");
                sb.Append(input.FilterText);
                sb.Append("%' ");
                skip1 += string.Format(" and Title like '%{0}%'", input.FilterText);
                query = query.Where(a => a.Title.Contains(input.FilterText));
            }
            if (skip1 != "")
            {
                skip = string.Format(skip, skip1);
            }

            var count = query.Count();

            var list = _sqlExecuter.SqlQuery<AreaFileListDto>(sb.ToString() + skip).ToList();

            return new PagedResultDto<AreaFileListDto>(count, list);
        }

        /// <summary>
        /// 根据条件获取地区数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ListResultDto<AreaFileListDto>> GetAreaByWhereAsync(GetAreaInput input)
        {
            var query = _areaRepositoryAsNoTrack;
            //TODO:根据传入的参数添加过滤条件
            #region 根据传入的参数添加过滤条件

            if (input.Level != 0)
                query = query.Where(p => p.Level == input.Level);

            #endregion

            var items = await query.ToListAsync();
            var fileList = _fileRepository.GetAll();

            return new ListResultDto<AreaFileListDto>(
                items.Select(item =>
                {
                    var dto = item.MapTo<AreaFileListDto>();
                    var fileModel = fileList.Where(p => p.Id == dto.FileID).Select(p => new { p.Url }).FirstOrDefault();
                    if (fileModel != null)
                        dto.Url = fileModel.Url;
                    return dto;
                }).ToList());
        }

        /// <summary>
        /// 通过指定id获取所有ParentID
        /// </summary>
        public async Task<AreaListDto> GetAreaParentIDByIdAsync(EntityDto<long> input)
        {
            var entity = await _areaRepository.GetAsync(input.Id);

            return entity.MapTo<AreaListDto>();
        }

        /// <summary>
        /// 获取全部地区
        /// </summary>
        /// <returns></returns>
        public async Task<ListResultDto<AreaListDto>> GetAreas()
        {
            var query = _areaRepository.GetAll().OrderBy(a => a.ParentId);

            var items = await query.ToListAsync();

            return new ListResultDto<AreaListDto>(
                items.Select(item =>
                {
                    var dto = item.MapTo<AreaListDto>();
                    return dto;
                }).ToList());
        }

        /// <summary>
        /// 获取全部地区树形数据
        /// </summary>
        /// <returns></returns>
        public List<ComboboxItemDto> GetAreaTree()
        {
            var list = GetAllAreas().Select(p => new ComboboxItemDto(
                 p.Id.ToString(),
                 p.DisplayTitle
                  )).ToList();
            list.Insert(0, new ComboboxItemDto("0", "无父级分类"));
            return list;
        }

        /// <summary>
        /// 根据层级获取地区数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ListResultDto<AreaListDto>> GetAreaByLevelAsync(GetAreaInput input)
        {
            var items = await _areaRepository.GetAll().Where(p => p.ParentId == input.Id).OrderByDescending(p => p.Id).ToListAsync();
            Area model = new Area()
            {
                Id = 0,
                Title = "请选择"
            };
            items.Insert(0, model);
            return new ListResultDto<AreaListDto>(
                items.Select(item =>
                {
                    var dto = item.MapTo<AreaListDto>();
                    return dto;
                }).ToList());
        }

        /// <summary>
        /// 树形排序
        /// </summary>
        /// <returns></returns>
        private List<AreaListDto> GetAllAreas()
        {
            var areas = _areaRepository.GetAll().Where(p => p.Level < 3).OrderBy(p => p.Id).ToList();
            var rootAreas = areas.Where(p => p.ParentId == 0 && p.Level < 3).OrderBy(p => p.Id);

            var result = new List<AreaListDto>();

            foreach (var rootArea in rootAreas)
            {
                var level = 0;
                AddArea(rootArea, areas, result, level);
            }

            return result;
        }

        /// <summary>
        /// 递归排序
        /// </summary>
        /// <param name="area"></param>
        /// <param name="allAreas"></param>
        /// <param name="result"></param>
        /// <param name="level"></param>
        private void AddArea(Area area, List<Area> allAreas, List<AreaListDto> result, int level)
        {
            var flatArea = area.MapTo<AreaListDto>();
            flatArea.Level = level;
            string layers = new string('—', area.Level);
            flatArea.DisplayTitle = layers + flatArea.Title;
            result.Add(flatArea);

            var child = allAreas.Where(i => i.ParentId == area.Id);
            if (child.Count() == 0)
            {
                return;
            }

            var children = allAreas.Where(p => p.ParentId != 0 && p.ParentId == area.Id).ToList();

            foreach (var childArea in children)
            {
                AddArea(childArea, allAreas, result, level + 1);
            }
        }

        /// <summary>
        /// 根据摄像头ID获取区域及其 乡镇、社区、村庄ID
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<AreaDto> GetAreaByCameraIdAsync(GetAreaInput input)
        {
            Expression<Func<Area, bool>> predicate = a => (a.CameraID == input.CameraID);
            var area = await _areaRepository.FirstOrDefaultAsync(predicate);
            if (area == null)
                return null;

            var areaDto = area.MapTo<AreaDto>();
            areaDto.TownID = 0;
            areaDto.AreaID = 0;
            areaDto.VillageID = 0;

            var level = area.Level;
            for (int i = level; i > 0; i--)
            {
                if (area == null)
                    break;

                if (area.Level == 1)
                {
                    areaDto.TownID = (int)area.Id;
                    break;
                }
                else if (area.Level == 2)
                    areaDto.AreaID = (int)area.Id;
                else if (area.Level == 3)
                    areaDto.VillageID = (int)area.Id;

                predicate = a => (a.Id == area.ParentId);
                area = await _areaRepository.FirstOrDefaultAsync(predicate);
            }

            return areaDto;
        }

        /// <summary>
        /// 根据父id获取子节点数量
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<AreaListDto>> GetAreaByParentIdAsync(EntityDto<long> input)
        {
            var query = _areaRepository.GetAll();
            query = query.Where(a => a.ParentId == input.Id);

            var count = await query.CountAsync();
            return new PagedResultDto<AreaListDto>(count, null);
        }
        #endregion


        #region 地区管理

        /// <summary>
        /// 根据查询条件获取地区分页列表
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area)]
        public async Task<PagedResultDto<AreaListDto>> GetPagedAreasAsync(GetAreaInput input)
        {
            var query = _areaRepositoryAsNoTrack;
            //TODO:根据传入的参数添加过滤条件
            if (!string.IsNullOrWhiteSpace(input.FilterText))
            {
                query = query.Where(p => p.Title.Contains(input.FilterText));
            }

            //区
            if (input.AreaID != 0)
                query = query.Where(p => p.ParentId == input.AreaID);
            else
            {
                //镇
                if (input.TownID != 0)
                    query = query.Where(p => p.ParentId == input.TownID);
            }

            var areaCount = 0;
            var areas = new List<Area>();

            #region 查询时，获取乡镇和社区的的子集目录
            if (!string.IsNullOrWhiteSpace(input.FilterText))//查询时，获取乡镇和社区的的子集目录
            {
                areas = await query
                .ToListAsync();//查询全部符合条件的

                var lAreas = areas.Where(a => a.Level == 1 || a.Level == 2).ToList();//筛选出级别为乡镇和社区的区域
                if (lAreas.Count > 0)
                {
                    var lAreaIds = lAreas.Select(a => a.Id).ToList();//拿到id
                    var lQuery = _areaRepositoryAsNoTrack;
                    lQuery = lQuery.Where(a => lAreaIds.Contains(a.ParentId));//设置查询子集条件：根据父id获取
                    var childArea = await lQuery.ToListAsync();//查询子集
                    var childAreaIds = childArea.Select(a => a.Id).ToList();//获取子集id

                    //合并id集
                    var list = lAreaIds;
                    list.AddRange(childAreaIds);
                    var newAreaIds = list;//获取全部id，准备重新查询

                    //重新组织查询linq
                    query = _areaRepositoryAsNoTrack;
                    query = query.Where(p => list.Contains(p.Id));
                }
            }
            #endregion

            areaCount = await query.CountAsync();
            areas = await query
            .OrderBy(p => p.ParentId)
            .PageBy(input)
            .ToListAsync();

            var areaListDtos = areas.MapTo<List<AreaListDto>>();
            #region 获取父节点信息
            if (areaListDtos.Count > 0)
            {
                var parentIds = areaListDtos.Select(a => (long)a.ParentId).ToList();
                var pQuery = _areaRepositoryAsNoTrack;
                pQuery = pQuery.Where(p => parentIds.Contains(p.Id));
                var pAreas = await pQuery.ToListAsync();
                foreach (var area in areaListDtos)
                {
                    if (area.ParentId > 0)
                    {
                        var pArea = pAreas.Where(p => p.Id == area.ParentId).FirstOrDefault();
                        if (pArea != null)
                        {
                            area.ParentName = pArea.Title;
                        }
                    }
                    else
                    {
                        area.ParentName = "无父级城市";
                    }
                }
            }
            #endregion

            return new PagedResultDto<AreaListDto>(
            areaCount,
            areaListDtos);
        }

        /// <summary>
        /// 通过Id获取地区信息进行编辑或修改 
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area)]
        public async Task<GetAreaForEditOutput> GetAreaForEditAsync(NullableIdDto<long> input)
        {
            var output = new GetAreaForEditOutput();

            AreaEditDto areaEditDto;

            if (input.Id.HasValue)
            {
                var entity = await _areaRepository.GetAsync(input.Id.Value);
                areaEditDto = entity.MapTo<AreaEditDto>();
            }
            else
            {
                areaEditDto = new AreaEditDto();
            }

            output.Area = areaEditDto;
            return output;
        }


        /// <summary>
        /// 通过指定id获取地区ListDto信息
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area)]
        public async Task<AreaListDto> GetAreaByIdAsync(EntityDto<long> input)
        {
            var entity = await _areaRepository.GetAsync(input.Id);

            return entity.MapTo<AreaListDto>();
        }

        /// <summary>
        /// 新增或更改地区
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area)]
        public async Task CreateOrUpdateAreaAsync(CreateOrUpdateAreaInput input)
        {
            if (input.AreaEditDto.Id.HasValue)
            {
                await UpdateAreaAsync(input.AreaEditDto);
            }
            else
            {
                await CreateAreaAsync(input.AreaEditDto);
            }
        }

        /// <summary>
        /// 新增地区
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area_CreateArea)]
        public virtual async Task<AreaEditDto> CreateAreaAsync(AreaEditDto input)
        {
            //TODO:新增前的逻辑判断，是否允许新增

            var entity = input.MapTo<Area>();

            entity.Id = await _areaRepository.InsertOrUpdateAndGetIdAsync(entity);
            entity = await _areaRepository.GetAsync(entity.Id);

            //查询父节点的深度赋值
            if (entity.ParentId > 0)
            {
                Area model = _areaRepository.FirstOrDefault(p => p.Id == entity.ParentId);
                entity.Level = model.Level + 1;
            }
            else
                entity.Level = 1;

            //修改节点列表和深度
            entity = await _areaRepository.UpdateAsync(entity);
            return entity.MapTo<AreaEditDto>();
        }

        /// <summary>
        /// 编辑地区
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area_EditArea)]
        public virtual async Task UpdateAreaAsync(AreaEditDto input)
        {
            //TODO:更新前的逻辑判断，是否允许更新

            var entity = await _areaRepository.GetAsync(input.Id.Value);
            input.MapTo(entity);
            if (input.ParentId == 0)
            {
                entity.Level = 1;
            }
            else
            {
                var query = _areaRepositoryAsNoTrack;
                query = query.Where(a => a.Id == input.ParentId);
                var areas = await query.ToListAsync();
                if (areas.Count > 0)
                {
                    entity.Level = areas[0].Level + 1;
                }
                else
                {
                    entity.ParentId = 0;
                    entity.Level = 1;
                }
            }

            await _areaRepository.UpdateAsync(entity);
        }

        /// <summary>
        /// 删除地区
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area_DeleteArea)]
        public async Task DeleteAreaAsync(EntityDto<long> input)
        {
            //TODO:删除前的逻辑判断，是否允许删除
            await _areaRepository.DeleteAsync(input.Id);
        }

        /// <summary>
        /// 批量删除地区
        /// </summary>
        [AbpAuthorize(AreaAppPermissions.Area_DeleteArea)]
        public async Task BatchDeleteAreaAsync(List<long> input)
        {
            //TODO:批量删除前的逻辑判断，是否允许删除
            await _areaRepository.DeleteAsync(s => input.Contains(s.Id));
        }

        #endregion

        #region 地区的Excel导出功能


        public async Task<FileDto> GetAreaToExcel()
        {
            var entities = await _areaRepository.GetAll().ToListAsync();

            var dtos = entities.MapTo<List<AreaListDto>>();

            var fileDto = _areaListExcelExporter.ExportAreaToFile(dtos);



            return fileDto;
        }


        #endregion

        #region IOS

        /// <summary>
        /// 根据层级获取地区数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<ListResultDto<AreaListDto>> GetAreaByLevelAsyncIOS(GetAreaIOSInput input)
        {
            int id = Convert.ToInt32(input.Id);
            var items = await _areaRepository.GetAll().Where(p => p.ParentId == id).OrderByDescending(p => p.Id).ToListAsync();

            return new ListResultDto<AreaListDto>(
                items.Select(item =>
                {
                    var dto = item.MapTo<AreaListDto>();
                    return dto;
                }).ToList());
        }

        #endregion

    }
}
